package nuc.ss.wl.dao.impl;

import nuc.ss.wl.dao.TeacherDao;
import nuc.ss.wl.entity.Teacher;
import nuc.ss.wl.utils.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class TeacherDaoImpl implements TeacherDao {
    @Override
    public int save(Teacher teacher) {
        String sql = "insert into teacher values(?,?)";
        Object[] params = {
                teacher.getTno(),
                teacher.getTname()
        };
        return DBUtil.executeUpdate(sql,params);
    }

    @Override
    public List<Teacher> findAll() {
        //声明jdbc变量
        Connection conn = null;
        PreparedStatement ps=null;
        ResultSet rs = null;

        //声明变量
        List<Teacher> list = null;
        try {
            //创建连接
            conn = DBUtil.getConnection();
            String sql = "select * from teacher";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();

            list = new ArrayList<Teacher>();
            while(rs.next()){
                int tno = rs.getInt(1);
                String tname = rs.getString(2);
                Teacher teacher = new Teacher();
                teacher.setTno(tno);
                teacher.setTname(tname);

                list.add(teacher);
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            DBUtil.closeAll(rs,ps,conn);
        }
        return list;
    }

    @Override
    public List<Teacher> find(int tno, String tname) {
       //声明jdbc变量
       Connection conn = null;
       PreparedStatement ps = null;
       ResultSet rs = null;
       //声明变量
        List<Teacher> list = null;
        try{
            conn = DBUtil.getConnection();
            StringBuilder sql = new StringBuilder("select * from teacher t where 1=1");

            if(tname != null && !"".equals(tname)){
                sql.append(" and t.tname like '%"+tname+"%'");
            }
            if(tno != 0){
                sql.append(" and t.tno ="+tno);
            }
            ps = conn.prepareStatement(sql.toString());
            rs = ps.executeQuery();
            list = new ArrayList<Teacher>();
            while(rs.next()){
                Teacher teacher = new Teacher();
                teacher.setTno(rs.getInt(1));
                teacher.setTname(rs.getString(2));
                list.add(teacher);
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            DBUtil.closeAll(rs,ps,conn);
        }
        return list;
    }

    @Override
    public int delete(int tno) {
        String sql = "delete from teacher where tno = ?";
        Object[] params = {tno};
        return DBUtil.executeUpdate(sql,params);
    }

    @Override
    public Teacher findById(int tno) {
        //声明jdbc变量
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        //声明变量
        Teacher teacher = null;
        try{
            conn = DBUtil.getConnection();
            String sql = "select * from teacher where tno = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,tno);
            rs = ps.executeQuery();
            while(rs.next()){
                String tname = rs.getString(2);
                teacher = new Teacher();
                teacher.setTno(tno);
                teacher.setTname(tname);

            }
        }catch(Exception e){
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(rs,ps,conn);
        }
        return teacher;
    }

    @Override
    public int update(Teacher teacher) {
        String sql = "update teacher set tname = ? where tno = ?";
        Object[] params = {
                teacher.getTname(),
                teacher.getTno()
        };
        return DBUtil.executeUpdate(sql,params);
    }
}
